package com.innovation.util.config;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ImportExcelData {

	/**
	 * @param args
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws Exception {
		long startTime = System.currentTimeMillis();
		String excelFilePath = System.getProperty("user.dir") + File.separator +"db" + File.separator + "snake_res.xls";
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		String userName = "ppseaer";
		String password = "ppseaer@ppsea.com";
		String dbUrl1 = "jdbc:mysql://127.0.0.1:3306/snake_res";
		Connection conn = DriverManager.getConnection(dbUrl1,userName, password);
		conn.setAutoCommit(false);
		Statement statement = conn.createStatement();
		
		// 创建Excel的工作书册 Workbook,对应到一个excel文档
		InputStream inputStream = new FileInputStream(excelFilePath);
	    HSSFWorkbook wb = new HSSFWorkbook(inputStream);
	    
	    //保存执行的sql语句
	    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
		Calendar cal = Calendar.getInstance();
		String time = sdf.format(cal.getTime());
	    FileWriter fr = new FileWriter(System.getProperty("user.dir") + File.separator +"db" + File.separator + "sql" + File.separator +"snake_res_"+time+".sql");
	    int sheetNum = wb.getNumberOfSheets();
	    for(int i = 0; i<sheetNum; i++){
	    	HSSFSheet sheet = wb.getSheetAt(i);
	    	Row header = sheet.getRow(0);
	    	int cellNum = header.getPhysicalNumberOfCells();
	    	
//	    	if(!sheet.getSheetName().equals("config_map")){
//	    		continue;
//	    	}
	    	StringBuilder prefix = new StringBuilder("insert into ").append(sheet.getSheetName());
	    	prefix.append(" (");
	    	for(int j = 0 ; j < cellNum; j++){
	    		String value = header.getCell(j).getStringCellValue();
	    		prefix.append(value);
	    		if(j != cellNum - 1){
	    			prefix.append(", ");
	    		}else{
	    			prefix.append(") values (");
	    		}
	    	}
	    	
	    	List<String> sqls = new ArrayList<String>();
	    	sqls.add("delete from "+sheet.getSheetName()+";");
	    	int dataRowIndex = 2;
	    	Row dataRow = sheet.getRow(dataRowIndex);
	    	while(dataRow != null && dataRow.getCell(0) != null && dataRow.getCell(0).getCellType()!=Cell.CELL_TYPE_BLANK){
	    		StringBuilder sql = new StringBuilder();
	    		sql.append(prefix.toString());
	    		for(int j = 0 ; j < cellNum; j++){
	    			String value = "";
	    			Cell cell = dataRow.getCell(j);
	    			if(cell != null){
	    				switch (cell.getCellType()) {
							case Cell.CELL_TYPE_BLANK:
							case Cell.CELL_TYPE_STRING:
								value = cell.getStringCellValue();
								break;
							case Cell.CELL_TYPE_NUMERIC:
								value = String.valueOf(cell.getNumericCellValue());
								break;
							default:
								value = cell.getStringCellValue();
								break;
						}
	    			}
		    		sql.append("'").append(value).append("'");
		    		if(j != cellNum - 1){
		    			sql.append(", ");
		    		}else{
		    			sql.append(");");
		    		}
	    		}
	    		sqls.add(sql.toString());
	    		
	    		dataRowIndex ++;
	    		dataRow = sheet.getRow(dataRowIndex);
	    	}
	    	
	    	for(String sql : sqls){
//	    		System.out.println("executing sql:"+sql);
	    		fr.write(sql);
	    		fr.write("\r\n");
	    		statement.execute(sql);
	    	}
	    	System.out.println((i+1)+".import "+sheet.getSheetName()+" data finished. rows="+(dataRowIndex-2));
	    }
	    
	    conn.commit();
	    statement.close();
	    conn.close();
	    
	    fr.flush();
	    fr.close();
	    
	    System.out.println();
	    System.out.println("import snake_res data finished. useTime="+(System.currentTimeMillis() - startTime));
	}

}
